﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

using System.Data;

namespace LI4.Data
{
    class Ligacao
    {
        public SqlConnection mySqlConnection;
        public String currentDatabase = "li4";
        public SqlCommand mySqlCommand;
        public SqlDataReader mySqlDataReader;

        public const int DBERROR = -1;
        public const int OK = 0;

        public Ligacao()
        {
            this.ConnectDB("li4");
        }

        //This is how i'm gona be closing my connection.
        private void closeConn()
        {
            if (mySqlConnection != null)
            {
                if (mySqlConnection.State == ConnectionState.Open)
                {
                    mySqlConnection.Close();
                }
                mySqlConnection.Dispose();
            }
        }

        //Here is where I create my connection.
        private void ConnectDB(string database)
        {
            // Here you define your server. Values can not be NULL.        //Database Server Name.
            string myDSN = "SQLEXPRESS";

            //Local Server Name.

            string myLSN = System.Environment.MachineName;

            //Define the type of security, 'TRUE' or 'FALASE'.
            string mySecType = "TRUE";

            //Here you have your connection string you can edit it here.
            string mySqlConnectionString = ("server=" + myLSN + "\\" + myDSN + ";database=" + database + ";Integrated Security=" + mySecType);

            //If you wish to use SQL security, well just make your own connection string...
            // I make sure I have declare what mySqlConnection stand for.
            if (mySqlConnection == null) { mySqlConnection = new SqlConnection(); };

            // Since i will be reusing the connection I will try this it the connection dose not exist.
            if (mySqlConnection.ConnectionString == string.Empty || mySqlConnection.ConnectionString == null)
            {
                // I use a try catch stament cuz I use 2 set of arguments to connect to the database
                try
                {
                    //First I try with a pool of 5-40 and a connection time out of 4 seconds. then I open the connection.
                    mySqlConnection.ConnectionString = "Min Pool Size=5;Max Pool Size=40;Connect Timeout=4;" + mySqlConnectionString + ";";
                    mySqlConnection.Open();
                }
                catch (Exception)
                {
                    //If it did not work i try not using the pool and I give it a 45 seconds timeout.
                    if (mySqlConnection.State != ConnectionState.Closed)
                    {
                        mySqlConnection.Close();
                    }
                    mySqlConnection.ConnectionString = "Pooling=false;Connect Timeout=45;" + mySqlConnectionString + ";";
                    mySqlConnection.Open();
                }

            }
            //Here if the connection exsist and is open i try this.
            if (mySqlConnection.State != ConnectionState.Open)
            {
                try
                {
                    mySqlConnection.ConnectionString = "Min Pool Size=5;Max Pool Size=40;Connect Timeout=4;" + mySqlConnectionString + ";";
                    mySqlConnection.Open();
                }
                catch (Exception)
                {
                    if (mySqlConnection.State != ConnectionState.Closed)
                    {
                        mySqlConnection.Close();
                    }
                    mySqlConnection.ConnectionString = "Pooling=false;Connect Timeout=45;" + mySqlConnectionString + ";";
                    mySqlConnection.Open();
                }
            }

        }

        public void Select(String query)
        {
            mySqlCommand = mySqlConnection.CreateCommand();
            mySqlCommand.CommandText = query;
            mySqlDataReader = mySqlCommand.ExecuteReader();
        }

        public void Update(String query)
        {
            mySqlCommand = mySqlConnection.CreateCommand();
            mySqlCommand.CommandText = query;
            mySqlCommand.ExecuteNonQuery();
        }

        public void runProcedure(String procedure, SqlParameter[] parameters)
        {
            mySqlCommand = mySqlConnection.CreateCommand();
            mySqlCommand.CommandText = procedure;
            mySqlCommand.CommandType = CommandType.StoredProcedure;
            mySqlCommand.Parameters.AddRange(parameters);

            mySqlDataReader = mySqlCommand.ExecuteReader();
        }

        public int runProcedure1(String procedure, SqlParameter[] parameters)
        {
            mySqlCommand = mySqlConnection.CreateCommand();
            mySqlCommand.CommandText = procedure;
            mySqlCommand.CommandType = CommandType.StoredProcedure;
            mySqlCommand.Parameters.AddRange(parameters);

            return (int)mySqlCommand.ExecuteScalar();
        }


    }
}



  
